package se2;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.ArrayList;
import java.util.List;

public class BillingPlan
{
    private int		id;
    private String	     name;
    private double	     baseRate;
    private List<DiscountPlan> discountPlans;

    public BillingPlan()
    {

    }

    public int getId()
    {
	return id;
    }

    public void setId(int id)
    {
	this.id = id;
    }

    public String getName()
    {
	return name;
    }

    public void setName(String name)
    {
	this.name = name;
    }

    public double getBaseRate()
    {
	return baseRate;
    }

    public void setBaseRate(double baseRate)
    {
	this.baseRate = baseRate;
    }

    public List<DiscountPlan> getDiscountPlans()
    {
	return discountPlans;
    }

    public void setDiscountPlans(List<DiscountPlan> discountPlans)
    {
	this.discountPlans = discountPlans;
    }

    private Connection getConnection() throws ClassNotFoundException, SQLException
    {
	Class.forName("org.sqlite.JDBC");
	String home = System.getProperty("user.home");
	Connection conn = DriverManager.getConnection("jdbc:sqlite:" + home + "/db/VoIP");

	return conn;
    }

    @SuppressWarnings("deprecation")
    /**
     * Loads discount plans for adding to a newly created BillingPlan
     */
    public boolean loadDiscounts(String discounts)
    {
	if (discounts.equals(""))
	{
	    this.discountPlans = new ArrayList<DiscountPlan>();
	}
	else
	{
	    // Query the database and pull out the corresponding DiscountPlans
	    // with the given ids
	    try
	    {
		String[] listOfDiscounts = discounts.split(",");
		List<DiscountPlan> discountPlans = new ArrayList<DiscountPlan>();

		Connection conn = getConnection();

		PreparedStatement ps;
		ResultSet rs;

		for (String id : listOfDiscounts)
		{
		    ps = conn.prepareStatement("select * from DiscountPlan where id = ?");
		    ps.setString(1, id);
		    rs = ps.executeQuery();

		    if (rs.next())
		    {
			DiscountPlan dp = new DiscountPlan();

			dp.setId(rs.getInt(1));
			dp.setName(rs.getString(2));
			dp.setStartDay(rs.getString(3));
			dp.setStartTime(rs.getTime(4));
			dp.setEndDay(rs.getString(5));
			dp.setEndTime(rs.getTime(6));
			dp.setRate(rs.getDouble(7));

			discountPlans.add(dp);
		    }
		    rs.close();
		}

		conn.close();

		this.discountPlans = discountPlans;

		System.out.println(discountPlans);
	    }
	    catch (Exception e)
	    {
		System.out.println("Failed in loading the DiscountPlans"
			+ " from the database into the BillingPlan object.");
		return false;
	    }

	    // Check for overlapping discount periods by determining the start
	    // and end times of the DiscountPlan. The day of the week of the
	    // DiscountPlan is mapped to an integer (Monday 0, Tuesday 1, etc.)
	    // and then an array of 10080 booleans, one for each minute, is
	    // populated. If the algorithm attempts to populate an array value
	    // already set to true, an overlap occurs.
	    boolean[] availability = new boolean[10080];
	    for (int i = 0; i < availability.length; i++)
	    {
		availability[i] = false;
	    }
	    int day, hour, minute;
	    int start, end;

	    for (DiscountPlan dp : discountPlans)
	    {
		// Get the elapsed time for the start time
		day = this.getDayOfWeek(dp.getStartDay());
		hour = dp.getStartTime().getHours();
		minute = dp.getStartTime().getMinutes();

		start = (day * 24 * 60) + (hour * 60 + minute);

		// Get the elapsed time for the end time
		day = this.getDayOfWeek(dp.getEndDay());
		hour = dp.getEndTime().getHours();
		minute = dp.getEndTime().getMinutes();

		end = (day * 24 * 60) + (hour * 60 + minute);

		for (int i = start; i <= end; i++)
		{
		    if (availability[i])
		    {
			System.out.println("Overlapping discount periods detected. Failing.");
			return false;
		    }
		    availability[i] = true;
		}
	    }
	}

	return true;
    }

    private int getDayOfWeek(String day)
    {
	day = day.toLowerCase();
	
	if (day.equals("monday"))
	{
	    return 0;
	}
	else if (day.equals("tuesday"))
	{
	    return 1;
	}
	else if (day.equals("wednesday"))
	{
	    return 2;
	}
	else if (day.equals("thursday"))
	{
	    return 3;
	}
	else if (day.equals("friday"))
	{
	    return 4;
	}
	else if (day.equals("saturday"))
	{
	    return 5;
	}
	else if (day.equals("sunday"))
	{
	    return 6;
	}
	
	return -1;
    }

    public boolean addBillingPlan()
    {
	try
	{
	    Connection conn = getConnection();

	    PreparedStatement ps;
	    ResultSet rs;

	    // Check for duplicates by name
	    ps = conn.prepareStatement("select * from BillingPlan where name = ?");
	    ps.setString(1, this.name);
	    rs = ps.executeQuery();

	    // If the ResulSet is populated, there is already a BillingPlan with the same name, fail.
	    if (rs.next())
	    {
		System.out.println("BillingPlan with given name already exists.");
		return false;
	    }
	    rs.close();

	    // Insert into database and get the auto-generated id
	    ps = conn.prepareStatement("insert into BillingPlan (name, base_rate) values (?, ?)");
	    ps.setString(1, this.name);
	    ps.setDouble(2, this.baseRate);
	    ps.execute();

	    // Query the database for the BillingPlan's unique id
	    ps = conn.prepareStatement("select id from BillingPlan where name = ?");
	    ps.setString(1, this.name);
	    rs = ps.executeQuery();

	    int newId = 0;
	    if (rs.next())
	    {
		newId = rs.getInt(1);
		id = newId;
	    }
	    rs.close();

	    // Add the DiscountPlan mappings to the BillingPlan
	    for (DiscountPlan dp : discountPlans)
	    {
		ps = conn.prepareStatement("insert into BillingToDiscount values (?, ?)");
		ps.setInt(1, this.id);
		ps.setInt(2, dp.getId());
		ps.execute();
	    }

	    conn.close();

	    return true;
	}
	catch (Exception e)
	{
	    System.out.println("Failed in adding a BillingPlan to the database (BillingPlan.java, addBillingPlan()).");
	    e.printStackTrace();
	    return false;
	}
    }

    public boolean editBillingPlan()
    {
	System.out.println ("Editing Billing Plan.");
	
	try
	{
	    Connection conn = getConnection();

	    PreparedStatement ps;
	    ResultSet rs;

	    // Check for duplicates by name
	    ps = conn.prepareStatement("select * from BillingPlan where id = ?");
	    ps.setInt(1, this.id);
	    rs = ps.executeQuery();

	    // If the ResulSet is populated, there is already a BillingPlan with
	    // the same id
	    if (!rs.next())
	    {
		System.out.println("No BillingPlan with this id exists (BillingPlan.java, updateBillingPlan()).");
		return false;
	    }
	    rs.close();

	    // Insert into database and get the auto-generated id
	    ps = conn.prepareStatement("update BillingPlan set name=?, base_rate=? where id=?");
	    ps.setString(1, this.name);
	    ps.setDouble(2, this.baseRate);
	    ps.setInt(3, this.id);
	    ps.execute();

	    // Add the DiscountPlan mappings to the BillingPlan
	    ps = conn.prepareStatement("delete from BillingToDiscount where plan_id=?");
	    ps.setInt(1, this.id);
	    ps.execute();
	    
	    for (DiscountPlan dp : discountPlans)
	    {
		ps = conn.prepareStatement("insert into BillingToDiscount values (?, ?)");
		ps.setInt(1, this.id);
		ps.setInt(2, dp.getId());
		ps.execute();
	    }

	    conn.close();

	    return true;
	}
	catch (Exception e)
	{
	    System.out.println("Failed in adding a BillingPlan to the database (BillingPlan.java, addBillingPlan()).");
	    e.printStackTrace();
	    return false;
	}
    }

    public boolean removeBillingPlan(int id)
    {
	int defaultBillingPlan = 0;

	try
	{
	    Connection conn = getConnection();

	    PreparedStatement ps;

	    ps = conn.prepareStatement("update BillingToPhone set plan_id = ? where plan_id = ?");
	    ps.setInt(1, defaultBillingPlan);
	    ps.setInt(2, id);

	    ps = conn.prepareStatement("delete from BillingPlan where id = ?");
	    ps.setInt(1, id);
	    ps.execute();

	    ps = conn.prepareStatement("delete from BillingToDiscount where plan_id = ?");
	    ps.setInt(1, id);
	    ps.execute();

	    conn.close();

	    return true;
	}
	catch (Exception e)
	{
	    System.out.println("Could not delete BillingPlan (BillingPlan.java, removeBillingPlan()).");
	    return false;
	}
    }

    /**
     * Loads all the BillingPlans from the database and returns it in List format
     * 
     * @return
     */
    public List<BillingPlan> getAllBillingPlans()
    {
	try
	{
	    Connection conn = getConnection();

	    PreparedStatement ps;
	    ResultSet rs;

	    ps = conn.prepareStatement("select * from BillingPlan");
	    rs = ps.executeQuery();

	    List<BillingPlan> billingPlans = new ArrayList<BillingPlan>();
	    while (rs.next())
	    {
		BillingPlan bp = new BillingPlan();
		bp.setId(rs.getInt(1));
		bp.setName(rs.getString(2));
		bp.setBaseRate(rs.getDouble(3));

		billingPlans.add(bp);
	    }
	    rs.close();

	    conn.close();

	    return billingPlans;
	}
	catch (Exception e)
	{
	    System.out.println("Failed in retrieving all BillingPlans (BillingPlan.java, getAllBillingPlans()).");
	    e.printStackTrace();
	    return null;
	}
    }

    public void loadAllInformation()
    {
	System.out.println(this.id);

	try
	{
	    Connection conn = getConnection();

	    PreparedStatement ps;
	    ResultSet rs;

	    // Load DiscountPlans
	    ps = conn
		    .prepareStatement("select * from DiscountPlan where id = (select disc_id from BillingToDiscount where plan_id = ?)");
	    ps.setInt(1, this.id);
	    rs = ps.executeQuery();

	    List<DiscountPlan> discountPlans = new ArrayList<DiscountPlan>();
	    while (rs.next())
	    {
		DiscountPlan dp = new DiscountPlan();
		
		dp.setId(rs.getInt(1));
		dp.setName(rs.getString(2));
		dp.setStartDay(rs.getString(3));
		dp.setStartTime(rs.getTime(4));
		dp.setEndDay(rs.getString(5));
		dp.setEndTime(rs.getTime(6));
		dp.setRate(rs.getDouble(7));
		
		discountPlans.add(dp);
	    }
	    rs.close();

	    this.discountPlans = discountPlans;

	    // Load BillingPlan information
	    ps = conn.prepareStatement("select * from BillingPlan where id = ?");
	    ps.setInt(1, this.id);
	    rs = ps.executeQuery();

	    if (rs.next())
	    {
		this.id = rs.getInt(1);
		this.name = rs.getString(2);
		this.baseRate = rs.getDouble(3);
	    }
	    rs.close();

	    conn.close();
	}
	catch (Exception e)
	{

	}
    }

}
